package ext.csc.component.numbergen.number;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import wt.method.RemoteAccess;
import wt.method.RemoteMethodServer;
import ext.csc.component.numbergen.connection.DBConn;


public class NumberManager implements RemoteAccess, Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1056608402346291948L;
	
	public static int CANCELLED_FLAG = 0;
	public static int GENERATED_FLAG = 1;
	public static int USED_FLAG = 2;
	public static int ALL_FLAG = 4;
	public static int APPROVED_FLAG = 5;		//For Jiuzhou
	
	public static String DESC = "desc";
	public static String ASC = "";
	
	public static String EQUAL = "=";
	public static String LIKE = "LIKE";

	
	/**
	 * Search Number Object by number
	 * @param number
	 * @return
	 */
	public static GeneratedNumber getNumber(String number){
		ArrayList result =  getNumbersBy(GeneratedNumber.GENERATEDNUMBER_GN_NUMBER, NumberManager.EQUAL, number, NumberManager.ALL_FLAG, "");
		if(result.size() > 0){ 
			return (GeneratedNumber)result.get(0);
		}else{
			return null;
		}
	}
	
	public static ArrayList getNumbersLikeNumStr(String numberString, int usedFlag, String seqOrder){
		ArrayList result =  getNumbersBy(GeneratedNumber.GENERATEDNUMBER_GN_NUMBER, NumberManager.LIKE, "%" + numberString + "%", usedFlag, seqOrder);
		return result;
	}
	
	/**
	 * Search Numbers by its class path
	 * @param classPath
	 * @param usedFlag
	 * @param orderby
	 * @return
	 */
	public static ArrayList getNumbersByClassPath(String classPath, int usedFlag, String seqOrder){
		ArrayList result =  getNumbersBy(GeneratedNumber.GENERATEDNUMBER_GN_CLASS, NumberManager.EQUAL, classPath, usedFlag, seqOrder);
		return result;
	}
	
	/**
	 * Search Numbers like its class path
	 * @param classPath
	 * @param usedFlag
	 * @param seqOrder
	 * @return
	 */
	public static ArrayList getNumberLikeClassPath(String classPath, int usedFlag, String seqOrder){
		ArrayList result =  getNumbersBy(GeneratedNumber.GENERATEDNUMBER_GN_CLASS, NumberManager.LIKE, classPath+ "%", usedFlag, seqOrder);
		return result;
	}
	
	/**
	 * Search Numbers by the requestor
	 * @param registerUser
	 * @param usedFlag
	 * @param orderby
	 * @return
	 */
	public static ArrayList getNumbersByRequestor(String requestor, int usedFlag, String seqOrder){
		ArrayList result = new ArrayList();
		if(requestor.equals(AdministrationHelper.ADMINISTRATOR)){
			result= getNumbersBy(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR, NumberManager.LIKE, "%", usedFlag, seqOrder);
		}else{
			result = getNumbersBy(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR, NumberManager.EQUAL, requestor, usedFlag, seqOrder);
		}
		return result;
	}
	
	/**
	private static ArrayList getNumbersBy(String searchBy, String operation, String searchValue, Integer usedFlag, String seqOrder){
		try {
			if (!RemoteMethodServer.ServerFlag) {
				return (ArrayList) RemoteMethodServer.getDefault().invoke("getNumbersByRemote", NumberManager.class.getName(), null,
						new Class[] {String.class, String.class, String.class, Integer.class, String.class},
						new Object[] {searchBy, operation, searchValue, usedFlag, seqOrder});
			} else {
				return getNumbersByRemote(searchBy, operation, searchValue, usedFlag, seqOrder);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ArrayList();
	}
	**/
	
	public static ArrayList getNumbersBy(String searchBy, String operation, String searchValue, Integer usedFlag, String seqOrder){
		ArrayList result = new ArrayList();
		
		DBConn conn = null;
		try {
			conn = new DBConn();
			
			String sql = "SELECT * FROM csc_generatednumber WHERE " +  searchBy + " " + operation + " '" + searchValue + "'";
			System.out.println(sql);
			
			if (usedFlag != ALL_FLAG){
				sql = sql + " AND " + GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + usedFlag;
			}
			sql = sql + " ORDER BY " + GeneratedNumber.GENERATEDNUMBER_GN_SEQ + " " + seqOrder;

			ResultSet rs = conn.executeQuery(sql);
			GeneratedNumber gNumber;
			while(rs.next()){
				gNumber = new GeneratedNumber(); 
				gNumber.setNumber(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_NUMBER));
				gNumber.setClasspath(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_CLASS));
				gNumber.setObjectname(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_NAME));
				gNumber.setFlag(rs.getInt(GeneratedNumber.GENERATEDNUMBER_GN_FLAG));
				gNumber.setRequestor(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR));
				gNumber.setDatetime(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDATETIME));
				gNumber.setRequestdesc(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC));
				gNumber.setCanceldesc(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC));
				gNumber.setSeq(rs.getInt(GeneratedNumber.GENERATEDNUMBER_GN_SEQ));
				result.add(gNumber);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	public static ArrayList getNumbersBySQL(String sql){
		ArrayList result = new ArrayList();
		
		if(sql.equals("") || sql == null) return result;
		
		DBConn conn = null;
		try {
			conn = new DBConn();
			
			System.out.println(sql);
			
			ResultSet rs = conn.executeQuery(sql);
			GeneratedNumber gNumber;
			while(rs.next()){
				gNumber = new GeneratedNumber(); 
				gNumber.setNumber(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_NUMBER));
				gNumber.setClasspath(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_CLASS));
				gNumber.setObjectname(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_NAME));
				gNumber.setFlag(rs.getInt(GeneratedNumber.GENERATEDNUMBER_GN_FLAG));
				gNumber.setRequestor(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR));
				gNumber.setDatetime(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDATETIME));
				gNumber.setRequestdesc(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC));
				gNumber.setCanceldesc(rs.getString(GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC));
				gNumber.setSeq(rs.getInt(GeneratedNumber.GENERATEDNUMBER_GN_SEQ));
				result.add(gNumber);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	public static int getMaxSequence(String classPath, Integer usedFlag){
		int result = -1;
		
		DBConn conn = null;
		try {
			conn = new DBConn();
			
			String sql = "SELECT Max(" + GeneratedNumber.GENERATEDNUMBER_GN_SEQ + ") AS MaxSeq FROM csc_generatednumber WHERE " +  
							GeneratedNumber.GENERATEDNUMBER_GN_CLASS + " " + 
							NumberManager.EQUAL + " '" + classPath + "' ";
			
			if (usedFlag != ALL_FLAG){
				sql = sql + " AND " + GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + usedFlag;
			}
			
			System.out.println(sql);
			
			ResultSet rs = conn.executeQuery(sql);
			if(rs.next()){
				if(rs.getString("MaxSeq") != null){
					result = Integer.parseInt(rs.getString("MaxSeq"));
				}
			}
		} catch (Exception e) {	
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
}
